'=====================================================================
'
'  File:    Program.vb
'  Summary: Creates an SSIS package which contains an SMO ForLoop enumeration.
'  Date:    April 25, 2005
'
'---------------------------------------------------------------------
'  This file is part of the Microsoft SQL Server Code Samples.
'  Copyright (C) Microsoft Corporation.  All rights reserved.
'
'  This source code is intended only as a supplement to Microsoft
'  Development Tools and/or on-line documentation.  See these other
'  materials for detailed information regarding Microsoft code samples.
'
'  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
'  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
'  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
'  PARTICULAR PURPOSE.
'======================================================= 

Class Program
    Private Const PACKAGEFILENAME As String = "\SamplePackage.dtsx"

    Public Shared Sub Main()
        CreateForEachSmoEnumeratorPackageWithProperties()
    End Sub

    Private Shared Sub CreateForEachSmoEnumeratorPackageWithProperties()
        Dim currentDirectory As String
        Dim application As Application
        Dim package As Package
        Dim connections As Connections
        Dim connectionManager As ConnectionManager
        Dim executables As Executables
        Dim forEachEnumeratorInfo As ForEachEnumeratorInfo
        Dim forEachLoop As ForEachLoop
        Dim forEachEnumeratorHost As ForEachEnumeratorHost
        Dim forEachSMOEnumerator As ForEachSMOEnumerator
        Dim scriptTaskHost As TaskHost
        Dim scriptTask As ScriptTask
        Dim executeSQLTaskHost As TaskHost
        Dim executeSQLTask As ExecuteSQLTask
        Dim forEachVariableMappings As ForEachVariableMappings
        Dim forEachVariableMapping As ForEachVariableMapping
        Dim status As DTSExecResult

        ' Create the SSIS application
        application = New Application()

        ' Create the new package
        package = New Package()
        package.Properties("PackageType").SetValue(package, DTSPackageType.DTSDesigner90)
        package.Properties("Name").SetValue(package, "SMO Enumerator Sample")
        package.Properties("Description").SetValue(package, "This is a sample for SMO enumerators")
        package.Properties("CreatorComputerName").SetValue(package, "MACHINENAME") ' System.Environment.MachineName;
        package.Properties("CreatorName").SetValue(package, "USERNAME") 'System.Environment.UserName;
        ' Add variables
        package.Variables.Add("SchemaName", False, "User", "*** EMPTY ***")
        package.Variables.Add("TableName", False, "User", "*** EMPTY ***")
        package.Variables.Add("TableObject", False, "User", New Object())

        ' Adds the ADO.NET connection manager to the package
        connections = package.Connections
        connectionManager = connections.Add("ADO.NET:SQL")
        '@design'& @"System.Data, "
        '& @"Version=2.0.0.0, "
        '& @"Culture=neutral, "
        '& @"PublicKeyToken=b77a5c561934e089");
        Console.WriteLine("ConnectionManager creation name: " _
            & connectionManager.Properties("CreationName").GetValue(connectionManager).ToString()) '
        'ToDo: Error processing original source shown below
        '            //& @"PublicKeyToken=b77a5c561934e089");
        '            Console.WriteLine(@"ConnectionManager creation name: "
        '-------------^--- Syntax error: ';' expected
        connectionManager.Properties("Name").SetValue(connectionManager, "localhost.AdventureWorks")
        connectionManager.Properties("ConnectionString").SetValue(connectionManager, "Data Source=localhost;" & "Initial Catalog=AdventureWorks;" & "Integrated Security=True;")

        ' Create ForEach SMO Loop task
        executables = package.Executables
        forEachLoop = CType(executables.Add("STOCK:FOREACHLOOP"), ForEachLoop) '
        forEachLoop.Properties("Name").SetValue(forEachLoop, "Foreach Loop Container")
        forEachLoop.Properties("Description").SetValue(forEachLoop, "Foreach Loop Container")
        forEachVariableMappings = forEachLoop.VariableMappings
        forEachVariableMapping = forEachVariableMappings.Add()
        forEachVariableMapping.VariableName = "TableObject"
        forEachVariableMapping.ValueIndex = 0

        Console.WriteLine("ForEachLoop creation name: " & forEachLoop.Properties("CreationName").GetValue(forEachLoop).ToString())
        forEachEnumeratorInfo = application.ForEachEnumeratorInfos("Foreach SMO Enumerator")
        forEachEnumeratorHost = forEachEnumeratorInfo.CreateNew()
        forEachLoop.ForEachEnumerator = forEachEnumeratorHost

        ' Setup the SMO enumerator
        forEachSMOEnumerator = CType(forEachEnumeratorHost.InnerObject, ForEachSMOEnumerator)
        forEachSMOEnumerator.EnumURN = "RuntimeServer[@Connection='" & connectionManager.ID & "']/Server[@Name='localhost']/" & "Database[@Name='AdventureWorks']" & "/SMOEnumObj[@Name='Tables']" & "/SMOEnumType[@Name='ObjectsPP']"

        ' Add Script Task to ForEach Loop task
        scriptTaskHost = CType(forEachLoop.Executables.Add("STOCK:SCRIPTTASK"), TaskHost)
        scriptTaskHost.Properties("Name").SetValue(scriptTaskHost, "Script Task")
        scriptTaskHost.Properties("Description").SetValue(scriptTaskHost, "Script Task")

        scriptTask = CType(scriptTaskHost.InnerObject, ScriptTask)
        scriptTask.SetUniqueVsaProjectName()
        Console.WriteLine("VsaProjectName: " & scriptTask.VsaProjectName)
        scriptTask.CodeProvider.PutSourceCode("dts://Scripts/" & scriptTask.VsaProjectName & "/ScriptMain.vsaitem", My.Resources.CodeFile)
        scriptTask.CodeProvider.PutSourceCode("dts://Scripts/" & scriptTask.VsaProjectName & "/" & scriptTask.VsaProjectName & ".vsaproj", My.Resources.ProjectFile)
        scriptTask.ReadOnlyVariables = "User::TableObject"
        scriptTask.ReadWriteVariables = "User::SchemaName,User::TableName"
        scriptTask.PreCompile = False

        ' Add Execute SQL Task to ForEach Loop task
        executeSQLTaskHost = CType(forEachLoop.Executables.Add("STOCK:SQLTASK"), TaskHost)
        executeSQLTaskHost.Properties("Name").SetValue(executeSQLTaskHost, "DBCC CHECK CONSTRAINTS")
        executeSQLTaskHost.Properties("Description").SetValue(executeSQLTaskHost, "Execute SQL Task")
        executeSQLTaskHost.SetExpression("SqlStatementSource", My.Resources.SqlStatement)
        executeSQLTask = CType(executeSQLTaskHost.InnerObject, ExecuteSQLTask)
        executeSQLTask.Connection = connectionManager.Name
        executeSQLTask.SqlStatementSource = "*** See Expressions ***"
        executeSQLTask.SqlStatementSourceType = SqlStatementSourceType.DirectInput

        ' Add precedence constraint from script task to execute SQL task
        forEachLoop.PrecedenceConstraints.Add(scriptTaskHost, executeSQLTaskHost)

        ' Validate the layout of the package
        status = package.Validate(package.Connections, Nothing, New PackageEvents(), Nothing)
        Console.WriteLine("Validation result: " + status.ToString())

        ' Save the package
        currentDirectory = Environment.CurrentDirectory
        application.SaveToXml(currentDirectory & PACKAGEFILENAME, package, Nothing)
        Console.WriteLine("Package saved to " & currentDirectory & PACKAGEFILENAME)
    End Sub
End Class